﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.Coolzon.Core.Repositories.SQL
{
    public class SqlVersionFileDataRepository:IVersionFileDataRepository
    {

        public bool AddVersionFileData(DbContext context, Data.VersionFileData versionFileData)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "insert into VersionFileData(Id,Data) values (@Id,@Data)";
                    cmd.Parameters.Add(new SqlParameter("Id", versionFileData.Id));
                    cmd.Parameters.Add(new SqlParameter("Data", versionFileData.Data));
              
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool UpdateVersionFileDataById(DbContext context, Data.VersionFileData versionFileData)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "update VersionFileData set Data = @Data where Id=@Id )";
                    cmd.Parameters.Add(new SqlParameter("Id", versionFileData.Id));
                    cmd.Parameters.Add(new SqlParameter("Data", versionFileData.Data));
                  
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool DeleteVersionFileDataById(DbContext context, Guid Id)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "delete from VersionFileData where Id=@Id ";
                    cmd.Parameters.Add(new SqlParameter("Id", Id));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Data.VersionFileData GetVersionFileDataById(DbContext context, Guid Id)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select Id, Changeset, ChangedUser, ChangedDate, Comment, FileDataId from VersionFileChangeset where Id=@Id ";
                        cmd.Parameters.Add(new SqlParameter("Id", Id));

                        DataSet oDataSet = new DataSet();
                        sqlDataAdapter.Fill(oDataSet);

                        Data.VersionFileData versionFileData = new Data.VersionFileData();
                        if (oDataSet.Tables[0].Rows.Count > 0)
                        {
                            byte[] dataByte = new byte[int.MaxValue];
                            versionFileData.Id = new Guid(oDataSet.Tables[0].Rows[0]["Id"].ToString());
                            versionFileData.Data =System.Text.Encoding.Default.GetBytes(oDataSet.Tables[0].Rows[0]["Data"].ToString());
                            return versionFileData;
                        }
                        else
                        {
                            return null;
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public PagedList<Data.VersionFileData> GetVersionFileDataList(DbContext context, int start, int limit)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from (select *, ROW_NUMBER() OVER(ORDER BY Id) as rownum from [VersionFileData]) a where a.rownum >= @paramStart and a.rownum <= @paramEnd";
                        cmd.Parameters.Add(new SqlParameter("paramStart", start));
                        cmd.Parameters.Add(new SqlParameter("paramEnd", start + limit));
                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        List<Data.VersionFileData> versionFileDataList = null;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            versionFileDataList = new List<Data.VersionFileData>();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Data.VersionFileData versionFileData = new Data.VersionFileData();
                                versionFileData.Id = new Guid(dr["Id"].ToString());
                                versionFileData.Data = System.Text.Encoding.Default.GetBytes(dr["Data"].ToString());

                                versionFileDataList.Add(versionFileData);
                            }
                            return new PagedList<Data.VersionFileData>(versionFileDataList, start, limit, versionFileDataList.Count);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
